<?php
class ModelNewsletterSubscriber extends Model { 
    public function getList($data = array()){
        $sql = "SELECT * FROM " . DB_PREFIX . "newsletter WHERE IFNULL(`email`, '') <> '' ";
        
        if (isset($data['filter_email']) && !is_null($data['filter_email'])) {
            $sql .= " AND LCASE(`email`) LIKE '%" . strtolower($this->db->escape(strtolower($data['filter_email']))) . "%'";
        }
           
        if (isset($data['filter_date']) && !is_null($data['filter_date'])) {
			$sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date']) . "')";
		}
        
        if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
            $sql .= " AND LCASE(`name`) LIKE '%" . strtolower($this->db->escape(strtolower($data['filter_name']))) . "%'";
        }
        
        if (isset($data['sort'])){
            $sql .= " ORDER BY " . $data['sort'];	
        }
        else {
            $sql .= " ORDER BY `email` ";	
        }
        
        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC";
        }
        else {
            $sql .= " ASC";
        }           
        
        if(isset($data['limit']) && (int)$data['limit'] > 0){
             $data['start'] = ($data['start'] - 1) * $data['limit'];
             $data['start'] = ($data['start'] < 0) ? 0 : $data['start'];
            
            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }
        
        $query = $this->db->query($sql);

        return $query->rows;
    }
    
    public function getTotalItems($data = array()){
        $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "newsletter WHERE IFNULL(`email`, '') <> ''";
        
        if (isset($data['filter_email']) && !is_null($data['filter_email'])) {
            $sql .= " AND LCASE(`email`) LIKE '%" . strtolower($this->db->escape(strtolower($data['filter_email']))) . "%'";
        }
        if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
            $sql .= " AND LCASE(`name`) LIKE '%" . strtolower($this->db->escape(strtolower($data['filter_name']))) . "%'";
        }        
        if (isset($data['filter_date']) && !is_null($data['filter_date'])) {
			$sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date']) . "')";
		}
        
        $query = $this->db->query($sql);
        
        return $query->row['total'];
    }
    
    public function insertSubscriber($data){
        $this->db->query("INSERT INTO " . DB_PREFIX . "newsletter SET email = '" . $this->db->escape($data['email']) . "', name = '" . $this->db->escape($data['name']) . "', date_added = NOW()");
    }
    
    public function editSubscriber($subscriber_id, $data){
        $this->db->query("UPDATE " . DB_PREFIX . "newsletter SET email = '" . $this->db->escape($data['email']) . "', name = '" . $this->db->escape($data['name']) . "' WHERE  subscriber_id = '" . (int)$subscriber_id . "'");
    }
    
    public function subscriberExists($email){
        $row = $this->db->query("SELECT * FROM " . DB_PREFIX . "newsletter WHERE email = '" . $this->db->escape($email) . "'");        
        
        return $row->num_rows ? true : false;
    }
    
    public function getSubscriber($subscriber_id){
        $row = $this->db->query("SELECT * FROM " . DB_PREFIX . "newsletter WHERE subscriber_id = '" . (int)$subscriber_id . "'");        
        
        return $row->num_rows ? $row->row : false;
    }
    
    public function delete($email){
        $this->db->query("DELETE FROM " . DB_PREFIX . "newsletter WHERE email = '" . $this->db->escape($email) . "'");
    }
	
	public function getListForCC($critical, $order = '', $limit = 0){
        if($critical){
            $sql = "SELECT * FROM " . DB_PREFIX . "newsletter WHERE `email` NOT IN (" . $critical .") AND IFNULL(`email`, '') <> '' "; //AND IFNULL(`name`, '') <> '' ";
        }
        else{
            $sql = "SELECT * FROM " . DB_PREFIX . "newsletter WHERE IFNULL(`email`, '') <> '' "; //AND IFNULL(`name`, '') <> '' ";
        }

        if($order){
            $sql .= ' ORDER BY ' . $order;
        }
        else{
            $sql .= ' ORDER BY subscriber_id ';
        }

        if($limit){
			$sql .= " LIMIT 0,$limit";
		}
		
        $query = $this->db->query($sql);

        return $query->rows;
    }

    public function getListEmptyName($order = '', $limit = 0){
        $sql = "SELECT * FROM " . DB_PREFIX . "newsletter WHERE IFNULL(`name`, '') = ''  AND IFNULL(`email`, '') <> '' ";

        if($limit){
            $sql .= " LIMIT 0,$limit";
        }

        if($order){
            $sql .= ' ORDER BY ' . $order;
        }
        else{
            $sql .= ' ORDER BY subscriber_id ';
        }

        $query = $this->db->query($sql);

        return $query->rows;
    }
}